package com.learning.railwayreservation.dao;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.learning.railwayreservation.vo.TrainVO;

public class TrainDao extends BaseDao {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		TrainDao tdao = new TrainDao();
		List<TrainVO> trainList = tdao.getTrainsFromTrainType("express");
		for (TrainVO trainVO : trainList) {
			System.out.print("\nMASTER TRAIN ID " + trainVO.getMasterTrainId());
			System.out.print("  ,ROUTE ID   " + trainVO.getRouteId());
		    System.out.print("  ,Train Name:  " + trainVO.getTrainName());
			System.out.print("  ,Train Type:  " + trainVO.getTrainType());
				}
		/*
		 * // TrainDao trdao = new TrainDao(); List<TrainVO> trainList2 =
		 * tdao.getTrain(); for (TrainVO trainVO : trainList2) {
		 * System.out.print("\nTrain Name: " + trainVO.getTrainName());
		 * System.out.print(", Train Number: " + trainVO.getTrainNumber());
		 * System.out.print(", Number of Boogies: " +
		 * trainVO.getNumberOfBoogies()); System.out.print(", Fully AC: " +
		 * trainVO.isFullyAc()); }
		 * 
		 * List<TrainVO> trainList3 = tdao.getTrainsFromTrainName(); for
		 * (TrainVO trainVO : trainList3) { System.out.print("\nTrain Name: " +
		 * trainVO.getTrainName()); System.out.print(", Train Number: " +
		 * trainVO.getTrainNumber()); System.out.print(", Number of Boogies: " +
		 * trainVO.getNumberOfBoogies()); System.out.print(", Fully AC: " +
		 * trainVO.isFullyAc()); }
		 */

	}

	public List<TrainVO> getAllTrains() {

		TrainDao tdao = new TrainDao();
		Connection conn = tdao.getConnection();
		List<TrainVO> trainList = new ArrayList<TrainVO>();
		TrainVO trainVO;

		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * from MASTER_TRAIN");

			while (rs.next()) {
				trainVO = new TrainVO();
				trainVO.setMasterTrainId(rs.getInt("MASTER_TRAIN_ID"));
				trainVO.setRouteId(rs.getInt("ROUTE_ID"));
				trainVO.setTrainName(rs.getString("TRAIN_NAME"));
				trainVO.setTrainType(rs.getString("TRAIN_TYPE"));

				trainList.add(trainVO);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return trainList;
	}

	public List<TrainVO> getTrain(int trainNumber) {

		// List<TrainVO>getTrain(){
		// String s1, s2;
		// int trainNumber = 0;
		// ;
		// System.out.println("\nenter Train number");

		// BufferedReader br = new BufferedReader(new
		// InputStreamReader(System.in));
		// boolean cont = true;

		// try {
		// trainNumber = new Integer(br.readLine()).intValue();
		// } catch (IOException e) {
		// TODO Auto-generated catch block
		// e.printStackTrace();
		// } catch (NumberFormatException nfe) {
		// System.out.println("Invalid Input");

		// }

		// System.out.println(trainNumber);

		TrainDao tdao = new TrainDao();
		Connection conn = tdao.getConnection();
		List<TrainVO> trainList = new ArrayList<TrainVO>();
		TrainVO trainVO;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT * from TRAIN where train_number ="
							+ trainNumber);
			System.out.println(trainNumber);
			while (rs.next()) {
				trainVO = new TrainVO();
				trainVO.setMasterTrainId(rs.getInt("MASTER_TRAIN_ID"));
				trainVO.setRouteId(rs.getInt("ROUTE_ID"));
				trainVO.setTrainName(rs.getString("TRAIN_NAME"));
				trainVO.setTrainType(rs.getString("TRAIN_TYPE"));

				trainList.add(trainVO);
			}


		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return trainList;
	}

	// return null;

	// }

	public List<TrainVO> getTrainsFromTrainName(String trainName) {

		// List<TrainVO>getTrain(){
		// String s1, s2;
		// String trainName = null;

		// System.out.println("\nenter Train name");

		// BufferedReader br = new BufferedReader(new
		// InputStreamReader(System.in));
		// boolean cont = true;

		// try {
		// trainName = br.readLine();

		// } catch (IOException e) {
		// TODO Auto-generated catch block
		// e.printStackTrace();
		// } catch (NumberFormatException nfe) {
		// System.out.println("Invalid Input");

		// }

		// System.out.println(trainName);

		TrainDao tdao = new TrainDao();
		Connection conn = tdao.getConnection();
		List<TrainVO> trainList = new ArrayList<TrainVO>();
		TrainVO trainVO;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT * from TRAIN where lower(train_name) like lower('%"
							+ trainName + "%')");
			System.out.println(trainName);
			while (rs.next()) {
				trainVO = new TrainVO();
				trainVO.setMasterTrainId(rs.getInt("MASTER_TRAIN_ID"));
				trainVO.setRouteId(rs.getInt("ROUTE_ID"));
				trainVO.setTrainName(rs.getString("TRAIN_NAME"));
				trainVO.setTrainType(rs.getString("TRAIN_TYPE"));

				trainList.add(trainVO);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return trainList;
	}

	// return null;

	// }

	public List<TrainVO> getTrainsFromTrainType(String trainType) {

		TrainDao tdao = new TrainDao();
		Connection conn = tdao.getConnection();
		List<TrainVO> trainList = new ArrayList<TrainVO>();
		TrainVO trainVO;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT * from MASTER_TRAIN where lower(train_type) like lower('%"
							+ trainType + "%')");
			System.out.println(trainType);
			while (rs.next()) {
				trainVO = new TrainVO();
				trainVO.setMasterTrainId(rs.getInt("MASTER_TRAIN_ID"));
				trainVO.setRouteId(rs.getInt("ROUTE_ID"));
				trainVO.setTrainName(rs.getString("TRAIN_NAME"));
				trainVO.setTrainType(rs.getString("TRAIN_TYPE"));

				trainList.add(trainVO);
			}


		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return trainList;
	}

	public List<TrainVO> getTrainsFullyAC(boolean fullyAC) {

		TrainDao tdao = new TrainDao();
		Connection conn = tdao.getConnection();
		String fullyACStr;
		List<TrainVO> trainList = new ArrayList<TrainVO>();
		TrainVO trainVO;
		if (fullyAC) {
			fullyACStr = "Y";
		} else {
			fullyACStr = "N";
		}

		try {
			PreparedStatement stmt = conn
					.prepareStatement("select * from train where FULLY_AC = ?");

			stmt.setString(1, fullyACStr);

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				trainVO = new TrainVO();
				trainVO.setMasterTrainId(rs.getInt("MASTER_TRAIN_ID"));
				trainVO.setRouteId(rs.getInt("ROUTE_ID"));
				trainVO.setTrainName(rs.getString("TRAIN_NAME"));
				trainVO.setTrainType(rs.getString("TRAIN_TYPE"));

				trainList.add(trainVO);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return trainList;
	}

}
